# coding=utf-8
import sys
import bysdk.auth
from bysdk.byclient import BYClient
import json
import mysql.connector;
import datetime
import toke
class by:


	def getBy(self,api,params):
		
		tk=toke.toke()
		
		token = bysdk.auth.Token(token=tk.getToke('by'))
		client = BYClient(token)

		#print(str(client.invoke('byai.openapi.calljob.list', '1.0.0', 'GET', params=params), encoding="utf-8"))

		return str(client.invoke(api, '1.0.0', 'GET', params=params), encoding="utf-8")

	def upDb(self,data):

		mydb = mysql.connector.connect(
		  host="localhost",		  # 数据库主机地址
		  user="root",	  # 数据库用户名
		  passwd="root",   # 数据库密码
		  database="weizandata"
		)

		mycursor = mydb.cursor()
		upCount=0
		for item in data:

			val=[]
			filed=[]
			for key in item:
				filed.append(key)
				val.append('\"'+str(item[''+key+''])+'\"')

			filedStr=','.join(filed)
			valStr=','.join(val)
			#ignore into
			#clients
			sql="INSERT ignore into by_data("+filedStr+",isRead)value("+valStr+",0)"
			#print(sql)
			mycursor.execute(sql)
			upCount=upCount+int(mycursor.rowcount)
			mydb.commit()
		return upCount

	def byupData(self):

		endCallTime=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S');

		startCallTime=(datetime.datetime.now()-datetime.timedelta(hours=196)).strftime('%Y-%m-%d %H:%M:%S')

		params = {
			'companyId':'44748',
			'pageNum':1,
			'pageSize':200,
		    'createDate':startCallTime,
		    'endDate':endCallTime
		}

		datjson=self.getBy(api='byai.openapi.calljob.list',params=params)
		# print(datjson)
		data=json.loads(datjson)['data']['list']
		listData=[]
		xiangqing=[]
		for item in data:
			callJobId=	item['callJobId']   
			aprList={
				'companyId':'44748',
				'callJobId':callJobId,
				'finishStatus':'0',
			    'pageSize':200
			}
			pages=json.loads(self.getBy(api='byai.openapi.calljob.calldone.list',params=aprList))['data']['pages']
			renwuxiangqing=json.loads(self.getBy(api='byai.openapi.calljob.detail.get',params={'callJobId':callJobId,'companyId':'44748'}))['data']
			callData=self.getAB_count(item['jobName'])
			renwuxiangqing['AB_Count']=callData[0]
			renwuxiangqing['averageCall']=callData[1]
			# print('renwuxiangqing==>',renwuxiangqing)
			xiangqing.append(renwuxiangqing);
			
			print('百应更新任务名称'+item['jobName'])
			#print('共'+str(pages)+'页,每页200条')
			for i in range(0,pages+2):

				aprList1={
					'companyId':'44748',
					'callJobId':callJobId,
					# 'finishStatus':'0',
			   		'pageSize':500,
			   		'pageNum':i
				}

				#print('第'+str(i)+'页')

				#print('%s%d%s'('第',i,'页'))

				datalist=json.loads(self.getBy(api='byai.openapi.calljob.calldone.list',params=aprList1))['data']['list']
				listData.extend(datalist)
				#print('循环条目：'+str(len(datalist)))
			#print(len(listData))
		
		print('百应更新：'+str(self.upDb(listData))+'条')

		print('xiangqing==>',len(xiangqing))

		fs=open('C:/phpstudy_pro/WWW/cscs.qumei.com/appApi/aa.json','w+')

		fs.write(json.dumps(xiangqing))

		fs.close()

	def getAB_count(self,jobName):

		mydb = mysql.connector.connect(
		  host="localhost",		  # 数据库主机地址
		  user="root",	  # 数据库用户名
		  passwd="root",   # 数据库密码
		  database="weizandata"
		)

		mycursor = mydb.cursor()

		#sql="SELECT COUNT(*) AS COUNT FROM by_data WHERE callJobName = '" + jobName + "' AND SUBSTR(resultList,31,1)IN('A','B');SELECT SUM(duration)/COUNT(*) FROM by_data WHERE callJobName = '20200807B2' AND duration>0;"
			#print(sql)
		sql="select * from (select count(*) as AB_count from by_data where callJobName = '" + jobName + "' and SUBSTR(resultList,31,1)in('A','B')) a JOIN (SELECT concat(round(SUM(duration)/COUNT(*)),'s') AS averageCall FROM by_data WHERE callJobName = '" + jobName + "' AND duration>0) b;"
		mycursor.execute(sql)

		results = mycursor.fetchall()

		#print('result==>', results)

		return results[0]


if __name__ == '__main__':

	# 免签名token模式调用
	# token = auth.Token(token='f33160b4db6d39a3ae2091ce99796372')
	# client = BYClient(token)
	endCallTime=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S');

	startCallTime=(datetime.datetime.now()-datetime.timedelta(hours=250)).strftime('%Y-%m-%d %H:%M:%S')

	params = {
		'companyId':'44748',
		'pageNum':1,
		'pageSize':200,
	    'createDate':startCallTime,
	    'endDate':endCallTime
	}
	db=by()

	#datjson=db.getBy(api='byai.openapi.calljob.list',params=params)
	#print(datjson)

	# xiangqing=db.getBy(api='byai.openapi.calljob.detail.get',params={'callJobId':'1783593','companyId':'44748'})

	#data=json.loads(datjson)['data']['list']
	#db.byupData()
	db.getAB_count('20200809B1')

	# fs=open('aa.json','w+')

	# fs.write(xiangqing)

	# fs.close()

	# print('data==>',xiangqing)

	# listData=[]
	# for item in data:
	# 	callJobId=	item['callJobId']   
	# 	aprList={
	# 		'companyId':'44748',
	# 		'callJobId':callJobId,
	# 		'finishStatus':'0',
	# 	    'pageSize':200
	# 	}
	# 	pages=json.loads(db.getBy(api='byai.openapi.calljob.calldone.list',params=aprList))['data']['pages']
	# 	print('百应更新任务名称'+item['jobName'])
	# 	print('共'+str(pages)+'页,每页200条')
	# 	for i in range(0,pages+2):

	# 		aprList1={
	# 			'companyId':'44748',
	# 			'callJobId':callJobId,
	# 			'finishStatus':'0',
	# 	   		'pageSize':500,
	# 	   		'pageNum':i
	# 		}

	# 		#print('第'+str(i)+'页')

	# 		#print('%s%d%s'('第',i,'页'))

	# 		datalist=json.loads(db.getBy(api='byai.openapi.calljob.calldone.list',params=aprList1))['data']['list']
	# 		listData.extend(datalist)
	# 		#print('循环条目：'+str(len(datalist)))
	# 	#print(len(listData))
	
	# print(db.upDb(listData))
